﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE [dbo].[bhp_NemoTranslateSet] (
	@FieldId		INT,
	@Record		INT,
	@Language	NCHAR(5),
	@User			INT,
	@Translate	NVARCHAR(MAX)
)
AS
BEGIN

	SET XACT_ABORT ON;

	BEGIN TRAN

		IF EXISTS (
			SELECT 
				1
			FROM 
				[bht_NemoTranslates] t
			WHERE
				record_id		= @Record
				AND field_id	= @FieldId
				AND lang_code	= @Language
		)
			UPDATE
				[bht_NemoTranslates]
			SET
				translate		= @Translate,
				mtime			= GETDATE(),
				muser			= @User
			WHERE
				record_id		= @Record
				AND field_id	= @FieldId
				AND lang_code	= @Language
		ELSE
			INSERT INTO [bht_NemoTranslates] (record_id, field_id, translate, lang_code, muser)
					VALUES (@Record, @FieldId, @Translate, @Language, @User)
		
	COMMIT

	DECLARE
		@Sql					NVARCHAR(1000),
		@TabName		NVARCHAR(100)
		
	SELECT 
		@TabName = 'T' + CAST(t2.id as NVARCHAR(100))
	FROM 
		sTree t1 
		INNER JOIN sTree t2 ON t1.parent = t2.id
	WHERE 
		t1.id = @FieldId
		
	SET @Sql = 'UPDATE ' + @TabName + ' SET mtime = GETDATE(), muser = ' + CAST(@User as NVARCHAR(100)) + ' WHERE idx = ' + CAST(@Record as NVARCHAR(100))
	EXEC sp_executesql @Sql

END